import openpyxl
import datetime
import random

# todo 改成自己的
fileName = r"C:\Users\86150\YandexDisk\工作\恒大\运营商文件\山西明琴\恒大充电通对接（生产环境）.xlsx"

# 计算UUID
def getUuid():
    sdf = datetime.datetime.now().strftime("%y%m%d%H%M%S%f")[:-3]
    sd = random.randint(100000000, 999999999)
    uuid = str(sdf) + str(sd)
    return uuid


# 读取Excel文件
xfile = openpyxl.load_workbook(fileName)
sheet = xfile['Sheet1']
uuid1 = getUuid()
operator_id = sheet.cell(row=4, column=3).value
operator_name = sheet.cell(row=5, column=3).value

# todo 需要在表里加两格数据
operator_short_name = sheet.cell(row=15, column=2).value
letter = sheet.cell(row=15, column=3).value

uuid2 = getUuid()
sys_operator_id = sheet.cell(row=20, column=3).value
sys_operator_secret = sheet.cell(row=22, column=3).value
sys_sign_secret = sheet.cell(row=23, column=3).value
sys_data_secret = sheet.cell(row=24, column=3).value
sys_data_secret_iv = sheet.cell(row=25, column=3).value
evcs_operator_id = operator_id
evcs_operator_secret = sheet.cell(row=6, column=3).value
evcs_sign_secret = sheet.cell(row=7, column=3).value
evcs_data_secret = sheet.cell(row=8, column=3).value
evcs_data_secret_iv = sheet.cell(row=9, column=3).value

# todo 根据开发还是生产更改row的值
# 生产
sys_req_url = sheet.cell(row=28, column=3).value
evcs_req_url = sheet.cell(row=12, column=3).value

# 开发
# sys_req_url = sheet.cell(row=27, column=3).value
# evcs_req_url = sheet.cell(row=11, column=3).value


# 打印原生SQL语句
print(
    f"""
    INSERT INTO hdsc_db.evcs.operator_info
    VALUES
     (
      '{uuid1}',
      2,
      '{operator_id}',
      '{operator_name}',
      'admin',
      '',
      NULL,
      '',
      NULL,
      now(),
      'admin',
      now(),
      'admin',
      1,
      3,
      now(),
     '{operator_short_name}',
     '{letter}');
     
    INSERT INTO hdsc_db.evcs.operator_relation
    VALUES
     (
      '{uuid2}',
      NULL,
      '{sys_operator_id}',
      '{sys_operator_secret}',
      '{sys_sign_secret}',
      '{sys_data_secret}',
      '{sys_data_secret_iv}',
      '{evcs_operator_id}',
      '{evcs_operator_secret}',
      '{evcs_sign_secret}',
      '{evcs_data_secret}',
      '{evcs_data_secret_iv}',
      '{sys_req_url}',
      '{evcs_req_url}',
      1,
      1,
      NULL,
      NULL,
      now(),
      'admin',
      now(),
      'admin',
     1 
     );
     """
)
